﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Configuration;
using Model;
using System.Data;
using System.Data.SqlClient;

namespace StuInfoDAL
{
    public class sqlHelper
    {
        //创建数据库链接字符串
        public static string sqlConn = ConfigurationManager.ConnectionStrings["sqlConn"].ConnectionString;


        /// <summary>
        /// 查询方法
        /// </summary>
        /// <param name="sql">查询sql语句</param>
        /// <param name="sqlParameters">参数列表</param>
        /// <returns>返回查询数据</returns>
        public static DataTable Query(string sql, params SqlParameter[] sqlParameters)
        {
            DataTable table = new DataTable();
            SqlConnection sqlConnection = new SqlConnection(sqlConn);
            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sql,sqlConnection);
            if (sqlParameters.Length>0)
            {
                sqlDataAdapter.SelectCommand.Parameters.AddRange(sqlParameters);
            }
            sqlDataAdapter.Fill(table);
            return table;
        }

        public static SqlDataReader Read(string sql, params SqlParameter[] sqlParameters)
        {
            SqlConnection sqlConnection = new SqlConnection(sqlConn);
            sqlConnection.Open();
            SqlCommand sqlCommand = new SqlCommand(sql,sqlConnection);
            if (sqlParameters.Length>0)
            {
                sqlCommand.Parameters.AddRange(sqlParameters);
            }
            SqlDataReader info = sqlCommand.ExecuteReader(CommandBehavior.CloseConnection);
            return info;
        }

        public static int NonQuery(string sql, params SqlParameter[] sqlParameters)
        {
            int num = 0;
            using (SqlConnection sqlConnection = new SqlConnection(sqlConn))
            {
                using (SqlCommand sqlCommand = new SqlCommand(sql,sqlConnection)) {
                    try
                    {
                        sqlConnection.Open();
                        if (sqlParameters.Length > 0)
                        {
                            sqlCommand.Parameters.AddRange(sqlParameters);
                        }
                        num = sqlCommand.ExecuteNonQuery();
                    }
                    finally {
                        if (sqlConnection.State == ConnectionState.Open)
                        {
                            sqlConnection.Close();
                        }
                    }
                }
            }
            return num;
        }

    }
}
